package com.hlw.hr_man.daoImpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.hlw.hr_man.Model.DeptModel;
import com.hlw.hr_man.dao.FindDeptDao;
import com.hlw.hr_man.util.JDBCUtil;

public class FindDeptDaoImpl implements FindDeptDao{

	@Override
	public int getAllDepts() {
		int scount = 0;
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select count(dept_id) scount from hr_department where dept_isdel=0 ");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			ResultSet rs = pst.executeQuery();
			if(rs.next()){
				scount = rs.getInt("scount");
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return scount;
	}

	@Override
	public int getAllDepts(String searchname) {
		
		int scount = 0;
		Connection con =  JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select count(dept_id) scount from  hr_department where dept_name like ?");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			
			pst.setString(1, "%"+searchname+"%");
			ResultSet rs = pst.executeQuery();
			if(rs.next()){
				scount = rs.getInt("scount");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return scount;
	}

	@Override
	public List<DeptModel> getDepts( int start, int pageSize) {
		List<DeptModel> dept = new ArrayList<DeptModel>();
		Connection con =  JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select dept_id,dept_name,dept_describe,dept_temp,dept_createtime,dept_isdel from hr_department where dept_isdel = 0 ");
		sql.append(" limit ?,?");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setInt(1, start);
			pst.setInt(2, pageSize);
			ResultSet  rs = pst.executeQuery();
			while(rs.next()){
				DeptModel dm = new DeptModel();
				dm.setDept_id(rs.getInt("dept_id"));
				dm.setDept_name(rs.getString("dept_name"));
				dm.setDept_describe(rs.getString("dept_describe"));
				dm.setDept_temp(rs.getString("dept_temp"));
				dm.setDept_createtime(rs.getString("dept_createtime"));
				dm.setDept_isdel(rs.getString("dept_isdel"));
				dept.add(dm);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return dept;
	}

	@Override
	public List<DeptModel> getDepts( int start, int pageSize, String searchname) {
		
		List<DeptModel> depts = new ArrayList<DeptModel>();
		Connection con =  JDBCUtil.getConnection();
		StringBuffer sql= new StringBuffer("select dept_id,dept_name,dept_describe,dept_temp,dept_createtime,dept_isdel from hr_department where ");
		sql.append("dept_name like ? ");
		sql.append("limit ?,?");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setString(1, "%"+searchname+"%");
			pst.setInt(2, start);
			pst.setInt(3, pageSize);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				DeptModel dm = new DeptModel();
				dm.setDept_id(rs.getInt("dept_id"));
				dm.setDept_name(rs.getString("dept_name"));
				dm.setDept_describe(rs.getString("dept_describe"));
				dm.setDept_temp(rs.getString("dept_temp"));
				dm.setDept_createtime(rs.getString("dept_createtime"));
				dm.setDept_isdel(rs.getString("dept_isdel"));
				depts.add(dm);
				
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return depts;
	}
	@Override
	public String findDeptNameByEnumber(String enumber) {
		String deptname=null;
		Connection con=JDBCUtil.getConnection();
		StringBuffer sql=new StringBuffer("select hr_department.dept_name from hr_department,hr_employee where hr_department.dept_id=hr_employee.dept_id and hr_employee.e_number=?");
		try {
			PreparedStatement pst=con.prepareStatement(sql.toString());
			pst.setString(1, enumber);
			ResultSet rs=pst.executeQuery();
			if(rs.next()){
				deptname=rs.getString("dept_name");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return deptname;
	}
	    
	/**
	 * 得到部门对象
	 */
	@Override
	public List<DeptModel> getDeptments() {
		// TODO Auto-generated method stub
		List<DeptModel>  depts = new ArrayList<DeptModel>();
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select dept_id,dept_name from hr_department where dept_isdel=0");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				DeptModel dept = new DeptModel();
				dept.setDept_id(rs.getInt("dept_id"));
				dept.setDept_name(rs.getString("dept_name"));
				depts.add(dept);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return depts;
	}

}
